package com.eet.jpa.repository;

import com.eet.jpa.entity.member.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 用户DAO层
 *
 * @author Administrator
 */
@Repository
public interface UserRepository extends JpaRepository<User, Integer> {

    /**
     * 通过邮件查询用户信息(HQL语句)
     *
     * @param email 邮件信息
     * @return 用户信息列表
     */
    @Query(" FROM User u WHERE u.email = :email ")
    List<User> findByEmail(String email);

    /**
     * 通过电话和邮件查询用户信息 (原生SQL)
     *
     * @param phone 电话号码
     * @param email 邮件信息
     * @return 用户信息集合
     */
    @Query(nativeQuery = true, value = " SELECT id, last_name, first_name, email, phone FROM tb_user WHERE email = :email AND phone = :phone ")
    List<User> findByPhoneAndEmail(String phone, String email);

    /**
     * 通过角色查询用户信息
     *
     * @param roleName 角色ID
     * @return 用户信息
     */
    @Query(nativeQuery = true, value = "SELECT u.id, " +
            "u.first_name, u.last_name, r.role_name, r.remark " +
            "FROM tb_user u, tb_role r, tb_role_user ru " +
            "WHERE u.id = ru.user_id AND r.id = ru.role_id " +
            "AND r.role_name LIKE CONCAT('%', :roleName, '%')")
    List<Map<String, Object>> findByRole(@Param("roleName") String roleName);
}
